#!/bin/sh

# PPH系统数据库初始化脚本
# 特性：锁库检测、自动恢复、完整性检查

set -e

DB_PATH="${DB_PATH:-/data/pph.sqlite}"
LOG_PREFIX="[DB-INIT]"

log_info() {
    echo "$LOG_PREFIX $(date '+%Y-%m-%d %H:%M:%S') INFO: $1"
}

log_error() {
    echo "$LOG_PREFIX $(date '+%Y-%m-%d %H:%M:%S') ERROR: $1" >&2
}

log_warn() {
    echo "$LOG_PREFIX $(date '+%Y-%m-%d %H:%M:%S') WARN: $1"
}

# 检查数据库是否被锁定
check_db_lock() {
    log_info "检查数据库锁定状态..."
    
    if [ -f "${DB_PATH}-wal" ] || [ -f "${DB_PATH}-shm" ]; then
        log_warn "发现WAL文件，可能存在未完成的事务"
        
        # 尝试恢复
        if timeout 10 sqlite3 "$DB_PATH" "PRAGMA integrity_check;" > /dev/null 2>&1; then
            log_info "数据库完整性检查通过"
        else
            log_error "数据库完整性检查失败，尝试恢复..."
            
            # 强制WAL检查点
            if timeout 5 sqlite3 "$DB_PATH" "PRAGMA wal_checkpoint(FULL);" > /dev/null 2>&1; then
                log_info "WAL检查点恢复成功"
            else
                log_error "WAL检查点恢复失败"
                return 1
            fi
        fi
    fi
    
    return 0
}

# 初始化数据库结构
init_db_schema() {
    log_info "初始化数据库结构..."
    
    # 检查表是否存在
    if sqlite3 "$DB_PATH" "SELECT name FROM sqlite_master WHERE type='table' AND name='pph_records';" | grep -q "pph_records"; then
        log_info "数据库表已存在，跳过初始化"
        return 0
    fi
    
    # 创建表结构
    sqlite3 "$DB_PATH" << 'EOF'
-- PPH记录表
CREATE TABLE IF NOT EXISTS pph_records (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    record_date TEXT NOT NULL,
    line_name TEXT NOT NULL,
    order_number TEXT NOT NULL,
    product_name TEXT NOT NULL,
    material_code TEXT,
    start_time TEXT NOT NULL,
    end_time TEXT NOT NULL,
    quantity INTEGER NOT NULL,
    total_people INTEGER NOT NULL,
    break_minutes INTEGER DEFAULT 0,
    calculated_pph REAL NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 目标值表
CREATE TABLE IF NOT EXISTS pph_targets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    material_code TEXT NOT NULL,
    product_name TEXT NOT NULL,
    line_name TEXT NOT NULL,
    target_pph REAL NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 预估值表
CREATE TABLE IF NOT EXISTS pph_estimates (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    material_code TEXT NOT NULL,
    product_name TEXT NOT NULL,
    line_name TEXT NOT NULL,
    estimate_pph REAL NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_pph_records_date ON pph_records(record_date);
CREATE INDEX IF NOT EXISTS idx_pph_records_line ON pph_records(line_name);
CREATE INDEX IF NOT EXISTS idx_pph_records_product ON pph_records(material_code);

-- 设置数据库优化参数（只在初始化时设置一次）
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA cache_size=10000;
PRAGMA temp_store=MEMORY;
PRAGMA mmap_size=268435456;
EOF

    if [ $? -eq 0 ]; then
        log_info "数据库结构初始化完成"
    else
        log_error "数据库结构初始化失败"
        return 1
    fi
}

# 数据库连接测试
test_db_connection() {
    log_info "测试数据库连接..."
    
    # 3秒超时测试
    if timeout 3 sqlite3 "$DB_PATH" "SELECT 1;" > /dev/null 2>&1; then
        log_info "数据库连接测试成功"
        return 0
    else
        log_error "数据库连接测试失败"
        return 1
    fi
}

# 主执行流程
main() {
    log_info "开始PPH数据库初始化..."
    
    # 创建数据目录
    mkdir -p "$(dirname "$DB_PATH")"
    
    # 检查数据库文件
    if [ ! -f "$DB_PATH" ]; then
        log_info "数据库文件不存在，创建新数据库: $DB_PATH"
        touch "$DB_PATH"
    else
        log_info "使用现有数据库: $DB_PATH"
    fi
    
    # 检查锁定状态
    if ! check_db_lock; then
        log_error "数据库锁定检查失败"
        exit 1
    fi
    
    # 初始化数据库结构
    if ! init_db_schema; then
        log_error "数据库结构初始化失败"
        exit 1
    fi
    
    # 测试连接
    if ! test_db_connection; then
        log_error "数据库连接测试失败"
        exit 1
    fi
    
    log_info "PPH数据库初始化完成"
}

# 执行主流程
main "$@"
